package com.github.chirspan.xaas.core.db.jdbc;


import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.*;

@Slf4j
public class DBUtils {

    public static final String SQL_CHAR_WHERE = "where";

    /**
     * 通过sql返回元数据信息
     *
     * @param sqlStr
     * @param conn
     * @return
     */
    public static List<String> getMetaDataBySql(Connection conn, String sqlStr) {
        List<String> meta = new ArrayList<>();
        if (StrUtil.containsIgnoreCase(sqlStr, SQL_CHAR_WHERE)) {
            StringBuilder fromBuilder = new StringBuilder(sqlStr);
            int n = sqlStr.toLowerCase().lastIndexOf("where") + 6;
            sqlStr = fromBuilder.insert(n, "1=0 and ").toString();
        } else {
            sqlStr = sqlStr + " where 1=0";
        }

        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sqlStr);
            pstmt.execute();
            ResultSetMetaData rsmd = pstmt.getMetaData();
            for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
                meta.add(rsmd.getColumnName(i));
            }
        } catch (SQLException e) {
            log.error(e.getMessage());
            throw new RuntimeException(e.getMessage());
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error(e.getMessage());
            }
        }

        return meta;

    }

    public static Object querySimpleData(Connection conn, String sqlStr) throws SQLException {
        Statement stmt = null;
        Object res = null;
        try {
            stmt = conn.createStatement();
            stmt.executeQuery(sqlStr);
            ResultSet rs = stmt.getResultSet();
            while (rs.next()) {
                res = rs.getObject(1);
            }
        } catch (SQLException e) {
            throw new SQLException("DBUtils.queryData error,errorSql:" + sqlStr + ";" + e.getMessage());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    log.error("DBUtils.queryData.pstmt close Exception:" + e.getMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    log.error("DBUtils.queryData.conn close Exception:" + e.getMessage());
                }
            }

        }
        return res;
    }

    public static List<LinkedHashMap<String, Object>> queryData(Connection conn, String sqlStr) throws SQLException {
        List<LinkedHashMap<String, Object>> list = new ArrayList<>();
        Statement stmt = null;
        try {
            conn.setAutoCommit(false);
            stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                    ResultSet.CONCUR_READ_ONLY);

            stmt.setFetchSize(500);
            stmt.executeQuery(sqlStr);
            ResultSet rs = stmt.getResultSet();
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                LinkedHashMap<String, Object> rowData = new LinkedHashMap<>();
                for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
                    //rowData.put(rsmd.getColumnName(i), rs.getObject(i));
                    if (rsmd.getColumnTypeName(i).equalsIgnoreCase("CLOB")) {
                        String colName = rsmd.getColumnName(i);
                        Object colValue = rs.getString(i);
                        rowData.put(colName, colValue);
                    } else if (rsmd.getColumnTypeName(i).equalsIgnoreCase("BLOB")) {

                        String colName = rsmd.getColumnName(i);
                        Object colValue = rs.getBytes(i);
                        rowData.put(colName, colValue);
                    } else {
                        String colName = rsmd.getColumnName(i);
                        Object colValue = rs.getObject(i);
                        rowData.put(colName, colValue);
                    }
                }
                list.add(rowData);
            }
        } catch (SQLException e) {
            throw new SQLException("DBUtils.queryData error,errorSql:" + sqlStr + ";" + e.getMessage());
        } finally {

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    log.error("DBUtils.queryData.pstmt close Exception:" + e.getMessage());
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    log.error("DBUtils.queryData.conn close Exception:" + e.getMessage());
                }
            }

        }

        return list;
    }


    public static void execUpdate(Connection conn, String sqlStr) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn
                    .prepareStatement(sqlStr);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static JSONArray getMetaDataBySql2(Connection conn, String sqlStr) {
        JSONArray metaArr = new JSONArray();
        if (StrUtil.containsIgnoreCase(sqlStr, SQL_CHAR_WHERE)) {
            StringBuilder fromBuilder = new StringBuilder(sqlStr);
            int n = sqlStr.toLowerCase().lastIndexOf("where") + 6;
            sqlStr = fromBuilder.insert(n, "1=0 and ").toString();
        } else {
            sqlStr = sqlStr + " where 1=0";
        }

        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sqlStr);
            pstmt.execute();
            ResultSetMetaData rsmd = pstmt.getMetaData();
            JSONObject meta;
            for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
                meta = new JSONObject();
                meta.put("name", rsmd.getColumnLabel(i));
                meta.put("typeName", rsmd.getColumnTypeName(i));
                meta.put("autoIncrement", rsmd.isAutoIncrement(i));
                meta.put("tableName", rsmd.getTableName(i));
                metaArr.add(meta);
            }
        } catch (SQLException e) {
            log.error(e.getMessage());
            throw new RuntimeException(e.getMessage());
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error(e.getMessage());
            }
        }

        return metaArr;
    }
}
